package Practice.dao.impl;

import Practice.dao.DBUtils;
import Practice.dao.UserDao;
import Practice.dao.model.Users;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hyhWTX
 * @version 1.0
 * @ClassName UserDaoImpl
 * @date 2022年08月14日 15:04
 * @Description: TODO (一句话描述以下该类的功能)
 */
public class UserDaoImpl extends DBUtils implements UserDao {
    @Override
    public void addUser(List<Users> users) {
        //使用jdbc的批量操纵进行批量添加
        try {
            super.getCon();
            String sql = "insert into tbl_user2 values(default,?,?,?,?)";
            pre = con.prepareStatement(sql);
            for(Users user : users){
                pre.setString(1,user.getUser_nick_name());
                pre.setDate(2,new Date(user.getUser_register_date().getTime()));
                pre.setInt(3,user.getUser_score());
                pre.setString(4,user.getUser_sex());
                pre.addBatch();//将预处理对象添加到批处理中
            }
            pre.executeBatch();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }

    }

    @Override
    public void delUser(Integer... ids) {
        try {
            //使用动态SQL删除数据
            super.getCon();
            //delete from tbl_user where user_id in (?,?,?,?,?,?,?,?)
            StringBuffer sql = new StringBuffer("delete from tbl_user2 where user_id in (");
            for(int userId : ids){
                sql.append("?").append(",");
            }
            sql.deleteCharAt(sql.length()-1);//删除最后一个逗号
            sql.append(")");
            pre = con.prepareStatement(sql.toString());
            //循环设置？号占位符的值
            for(int i=0;i<ids.length;i++){
                pre.setInt(i+1,ids[i]);
            }

            pre.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }
    }

    @Override
    public void updateById(int userId, int score) {
        String sql = "update tbl_user2 set user_score=? where user_id=?";
        super.excute(sql,score,userId);
    }

    @Override
    public List<Users> queryMonth(int month) {
        try {
            String sql = "select * from tbl_user2 where MONTH(user_register_date)=?";
            super.getCon();
            pre = con.prepareStatement(sql);
            pre.setInt(1,month);
            res = pre.executeQuery();
            List<Users> users = new ArrayList<>();
            while(res.next()){

                Users user = new Users();
                user.setUser_id(res.getInt(1));
                user.setUser_nick_name(res.getString(2));
                user.setUser_register_date(res.getDate(3));
                user.setUser_score(res.getInt(4));
                user.setUser_sex(res.getString(5));
                users.add(user);
            }
            return users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }
        return null;
    }

    @Override
    public Users queryId(int userId) {

        try {
            String sql = "select * from tbl_user2 where user_id=?";
            super.getCon();
            pre = con.prepareStatement(sql);
            pre.setInt(1,userId);
            res = pre.executeQuery();

            if(res.next()){

                Users user = new Users();
                user.setUser_id(res.getInt(1));
                user.setUser_nick_name(res.getString(2));
                user.setUser_register_date(res.getDate(3));
                user.setUser_score(res.getInt(4));
                user.setUser_sex(res.getString(5));
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }

        return null;
    }

    @Override
    public List<Users> queryUserScore(int score) {

        try {
            String sql = "select * from tbl_user2 where user_score>?";
            super.getCon();
            pre = con.prepareStatement(sql);
            pre.setInt(1,score);
            res = pre.executeQuery();
            List<Users> users = new ArrayList<>();
            while(res.next()){

                Users user = new Users();
                user.setUser_id(res.getInt(1));
                user.setUser_nick_name(res.getString(2));
                user.setUser_register_date(res.getDate(3));
                user.setUser_score(res.getInt(4));
                user.setUser_sex(res.getString(5));
                users.add(user);
            }
            return  users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }

        return null;
    }

    @Override
    public List<Users> queryUserName(String nickName) {

        try {
            String sql = "select * from tbl_user2 where user_nick_name like concat('%',?,'%')";
            super.getCon();
            pre = con.prepareStatement(sql);
            pre.setString(1,nickName);
            res = pre.executeQuery();
            List<Users> users = new ArrayList<>();
            while(res.next()){

                Users user = new Users();
                user.setUser_id(res.getInt(1));
                user.setUser_nick_name(res.getString(2));
                user.setUser_register_date(res.getDate(3));
                user.setUser_score(res.getInt(4));
                user.setUser_sex(res.getString(5));
                users.add(user);
            }
            return  users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally{
            super.Close();
        }
        return null;
    }
}